#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-19
"""
The tmpo_vehicle_unavailable table holds data pertaining to maintenance
or any other event that might keep a vehicle from being assignable as a
mission resource. This API manages all access to or modification of that
 table.

    +----------------------+-----------------+--------------------------+
    |     Column Name      |       Type      |       Description        |
    +----------------------+-----------------+--------------------------+
    | vu_id                | int,pk,identity | Unique record identifier |
    | vehicle_id           | int, fk         | ref. vehicles table      |
    | description          | varchar         | text description         |
    | type                 | int, fk         | ref. vu_type table       |
    | vu_start             | datetime        | event start date/time    |
    | vu_end               | datetime        | event end date/time      |
    | service_location     | varchar         | event location           |
    | tow                  | bit             | Logical towing flag      |
    | accident             | bit             | Logical accident flag    |
    | mileage              | int             | Numeric mileage value    |
    | active               | bit             | Logical delete flag      |
    +----------------------+-----------------+--------------------------+

Information from this table is referenced by some of the other APIs, through
the vu_id key.

The functions found in this API are:

 get_maint_info
    selects all information through the use of a few joins, tying the 
    vehicles, unavailability, and descriptions together. The filter is
    an arbitrary time/date range of the current day to 100 days into
    the future, or a particular state of maintenance.

 add_maint_event
    inserts a maintenance event into the database, reading a form for all
    the required information.
    
 update_maint_event
    updates a maintenance record, using the vu_id as the filter. Reads a
    form for all fields (required or not).

 delete_maint_event
   flags a record as 'inactive' in the database, uses the vu_id value
   as the filter. This is a logical delete, and not a physical one - such
   that the criteria for 'deleted' is that active has a 0 value, and 'active'
   has a '1' value.

"""
from string import Template
from tack.db import query, sql_string, sql_int, sql_date
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.dicttools import query_to_list
import time

select_maint_info = """
SELECT
    tvu.vu_id,
    tv.vehicleno,
    tv.tagno,
    tvu.description,
    tvu.type,
    tvu.vu_start,
    tvu.vu_end,
    tvu.service_location,
    tvu.tow,
    tvu.accident,
    tvu.mileage,
    tvt.description,
    tvu.vehicle_id
FROM
    tmpo_vehicle_unavailable tvu,
    tmpo_vehicle tv,
    tmpo_vu_type tvt
WHERE
    tv.vehicle_id = tvu.vehicle_id 
    AND tvu.type=tvt.vu_type_id 
    AND ((getdate() < tvu.vu_end 
    AND getdate()+100 > tvu.vu_start) 
      OR tvu.type = 2) 
    AND tvu.active=1
ORDER BY tvu.vu_start ASC
"""

insert_maint_event = """
INSERT INTO 
    tmpo_vehicle_unavailable(
      vehicle_id,
      description,
      type,
      vu_start,
      vu_end,
      service_location,
      tow,
      accident,
      mileage)
VALUES(
    $vehicle_id,
    $reason,
    $status,
    $start,
    $finish,
    $location,
    $tow,
    $accident,
    $mileage )
"""

maint_event_update = """
UPDATE
    tmpo_vehicle_unavailable
SET 
    type=$status,
    vu_start=$start,
    vu_end=$finish,
    description=$reason,
    mileage=$mileage,
    service_location=$location,
    tow=$tow,
    accident=$accident
WHERE
    vu_id= $vu_id
"""

maint_event_delete = """
UPDATE
    tmpo_vehicle_unavailable
SET
    active=0
WHERE
    vu_id = $vu_id
"""

def get_maint_info(environ, start_response):
    sql = select_maint_info
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','vehicleno','tagno','reason','type','start','finish',
              'location','tow','accident','mileage','status','vehicle_id'] 
    output['maintenance'] = query_to_list(rows, labels)
    return json_app(output)(environ, start_response)

def add_maint_event(environ, start_response):
    form = environ['form']
    status = sql_int(form['status'])
    vehicle_id = sql_int(form['vehicle_id'])
    mileage = sql_int(form['mileage'])
    start = sql_date(form['start'], '%Y-%m-%d %H:%M')
    finish = sql_date(form['finish'], '%Y-%m-%d %H:%M')
    location = sql_string(form['location'])
    reason = sql_string(form['reason'])
    tow = sql_int(form['tow'])
    accident = sql_int(form['accident'])
    sql = Template(insert_maint_event).substitute(locals())
    query(environ['connection'], sql)
    rows = query(environ['connection'], 'SELECT CAST(@@IDENTITY AS INT)')
    return json_app(rows)(environ, start_response)

def update_maint_event(environ, start_response):
    vu_id = environ['dispatcher.groups']['vu_id']
    status = sql_int(environ['form']['status'])
    mileage = sql_int(environ['form']['mileage'])
    start = sql_date(environ['form']['start'], '%Y-%m-%d %H:%M')
    finish = sql_date(environ['form']['finish'], '%Y-%m-%d %H:%M')
    location = sql_string(environ['form']['location'])
    reason = sql_string(environ['form']['reason'])
    tow = sql_int(environ['form']['tow'])
    accident = sql_int(environ['form']['accident'])
    sql = Template(maint_event_update).substitute(locals())
    rows = query(environ['connection'], sql)
    output = 'Maintenance Event Updated'
    return string_app(output)(environ, start_response)

def delete_maint_event(environ, start_response):
    vu_id = environ['dispatcher.groups']['vu_id']
    sql = Template(maint_event_delete).substitute(locals())
    rows = query(environ['connection'], sql)
    output = 'Maintenance Event Deleted'
    return string_app(output)(environ, start_response)
